import pandas as pd
import tkinter as tk
from tkinter import filedialog, ttk, messagebox
import os

class ExcelDataAnalyzer:
    def __init__(self, root):
        self.root = root
        self.root.title("核心网开户数据统计分析")
        self.root.geometry("600x400")
        self.root.resizable(True, True)
        
        # 设置中文字体
        self.style = ttk.Style()
        self.style.configure(
            "TLabel", 
            font=("SimHei", 10),
            padding=5
        )
        self.style.configure(
            "TButton", 
            font=("SimHei", 10),
            padding=5
        )
        self.style.configure(
            "Header.TLabel", 
            font=("SimHei", 12, "bold"),
            padding=10
        )
        
        # 存储结果数据
        self.file_path = ""
        self.max_value_46 = None  # 192开卡总数量
        self.max_value_28 = None  # 固话开户用户数
        self.sum_value = None     # 核心网在线用户数
        
        # 创建界面
        self.create_widgets()
    
    def create_widgets(self):
        # 创建主框架
        main_frame = ttk.Frame(self.root, padding="20")
        main_frame.pack(fill=tk.BOTH, expand=True)
        
        # 文件选择区域
        file_frame = ttk.Frame(main_frame)
        file_frame.pack(fill=tk.X, pady=10)
        
        self.file_label = ttk.Label(file_frame, text="未选择文件", width=40)
        self.file_label.pack(side=tk.LEFT, padx=5)
        
        browse_btn = ttk.Button(file_frame, text="浏览文件", command=self.browse_file)
        browse_btn.pack(side=tk.RIGHT, padx=5)
        
        analyze_btn = ttk.Button(file_frame, text="分析数据", command=self.analyze_data)
        analyze_btn.pack(side=tk.RIGHT, padx=5)
        
        # 结果显示区域
        result_frame = ttk.LabelFrame(main_frame, text="统计结果", padding="10")
        result_frame.pack(fill=tk.BOTH, expand=True, pady=10)
        
        # 创建结果标签
        ttk.Label(result_frame, text="192开卡总数量:", anchor=tk.W).grid(row=0, column=0, sticky=tk.W, pady=5)
        self.result_label_46 = ttk.Label(result_frame, text="--", anchor=tk.W, width=20)
        self.result_label_46.grid(row=0, column=1, sticky=tk.W, pady=5)
        
        ttk.Label(result_frame, text="固话开户用户数:", anchor=tk.W).grid(row=1, column=0, sticky=tk.W, pady=5)
        self.result_label_28 = ttk.Label(result_frame, text="--", anchor=tk.W, width=20)
        self.result_label_28.grid(row=1, column=1, sticky=tk.W, pady=5)
        
        ttk.Label(result_frame, text="核心网在线用户数:", anchor=tk.W).grid(row=2, column=0, sticky=tk.W, pady=5)
        self.result_label_sum = ttk.Label(result_frame, text="--", anchor=tk.W, width=20)
        self.result_label_sum.grid(row=2, column=1, sticky=tk.W, pady=5)
        
        # 调整列宽
        result_frame.grid_columnconfigure(0, weight=1)
        result_frame.grid_columnconfigure(1, weight=1)
        
        # 状态信息区域
        status_frame = ttk.Frame(main_frame)
        status_frame.pack(fill=tk.X, pady=10)
        
        self.status_label = ttk.Label(status_frame, text="就绪", foreground="blue")
        self.status_label.pack(anchor=tk.W)
    
    def browse_file(self):
        """浏览并选择Excel文件"""
        file_types = [("Excel文件", "*.xls;*.xlsx"), ("所有文件", "*.*")]
        self.file_path = filedialog.askopenfilename(
            title="选择Excel文件",
            filetypes=file_types,
            initialdir=os.getcwd()
        )
        
        if self.file_path:
            # 显示文件名而不是完整路径
            file_name = os.path.basename(self.file_path)
            self.file_label.config(text=file_name)
            self.status_label.config(text=f"已选择文件: {file_name}", foreground="green")
            # 重置结果标签
            self.reset_results()
        else:
            self.status_label.config(text="未选择文件", foreground="blue")
    
    def reset_results(self):
        """重置结果标签"""
        self.max_value_46 = None
        self.max_value_28 = None
        self.sum_value = None
        self.result_label_46.config(text="--")
        self.result_label_28.config(text="--")
        self.result_label_sum.config(text="--")
    
    def analyze_data(self):
        """分析Excel文件数据"""
        if not self.file_path:
            messagebox.showwarning("警告", "请先选择一个Excel文件")
            return
        
        try:
            self.status_label.config(text="正在分析数据...", foreground="orange")
            self.root.update()
            
            # 从第11行开始读取Excel文件
            df = pd.read_excel(self.file_path, skiprows=10)
            
            # 定义目标列名
            target_license_column = 'LICENSE控制项测量'
            target_value_column = 'License控制项实际用户数 (个数)'
            target_time_column = '开始时间'
            
            # 检查目标列是否存在
            if target_license_column not in df.columns or target_value_column not in df.columns:
                missing_columns = []
                if target_license_column not in df.columns:
                    missing_columns.append(target_license_column)
                if target_value_column not in df.columns:
                    missing_columns.append(target_value_column)
                messagebox.showerror("错误", f"未找到以下列，请检查文件格式: {', '.join(missing_columns)}")
                self.status_label.config(text="分析失败", foreground="red")
                return
            
            # 处理前两个字符为'46'的情况 (192开卡总数量)
            filtered_df_46 = df[df[target_license_column].astype(str).str.startswith('46')]
            if not filtered_df_46.empty:
                filtered_df_46[target_value_column] = pd.to_numeric(filtered_df_46[target_value_column], errors='coerce')
                self.max_value_46 = filtered_df_46[target_value_column].max()
            
            # 处理前两个字符为'28'的情况 (固话开户用户数)
            filtered_df_28 = df[df[target_license_column].astype(str).str.startswith('28')]
            if not filtered_df_28.empty:
                filtered_df_28[target_value_column] = pd.to_numeric(filtered_df_28[target_value_column], errors='coerce')
                self.max_value_28 = filtered_df_28[target_value_column].max()
            
            # 处理开始时间最后为'18:00:00'且LICENSE控制项测量开头为'58'和'45'的情况
            value_58 = None
            value_45 = None
            
            if target_time_column in df.columns:
                # 筛选'开始时间'列最后字符串为'18:00:00'的行
                df[target_time_column] = df[target_time_column].astype(str)
                time_filtered_df = df[df[target_time_column].str.endswith('18:00:00')]
                
                if not time_filtered_df.empty:
                    # 筛选LICENSE控制项测量开头为'58'的行
                    filtered_df_58 = time_filtered_df[time_filtered_df[target_license_column].astype(str).str.startswith('58')]
                    if not filtered_df_58.empty:
                        filtered_df_58[target_value_column] = pd.to_numeric(filtered_df_58[target_value_column], errors='coerce')
                        if not filtered_df_58[target_value_column].dropna().empty:
                            value_58 = filtered_df_58[target_value_column].dropna().iloc[0]
                    
                    # 筛选LICENSE控制项测量开头为'45'的行
                    filtered_df_45 = time_filtered_df[time_filtered_df[target_license_column].astype(str).str.startswith('45')]
                    if not filtered_df_45.empty:
                        filtered_df_45[target_value_column] = pd.to_numeric(filtered_df_45[target_value_column], errors='coerce')
                        if not filtered_df_45[target_value_column].dropna().empty:
                            value_45 = filtered_df_45[target_value_column].dropna().iloc[0]
                    
                    # 计算两个值的和
                    if value_58 is not None and value_45 is not None:
                        self.sum_value = value_58 + value_45
            
            # 更新结果显示
            self.update_results()
            
            self.status_label.config(text="分析完成", foreground="green")
        
        except Exception as e:
            messagebox.showerror("错误", f"分析过程中发生错误: {str(e)}")
            self.status_label.config(text="分析失败", foreground="red")
    
    def update_results(self):
        """更新结果标签显示"""
        if self.max_value_46 is not None:
            self.result_label_46.config(text=str(self.max_value_46))
        else:
            self.result_label_46.config(text="未找到数据")
        
        if self.max_value_28 is not None:
            self.result_label_28.config(text=str(self.max_value_28))
        else:
            self.result_label_28.config(text="未找到数据")
        
        if self.sum_value is not None:
            self.result_label_sum.config(text=str(self.sum_value))
        else:
            self.result_label_sum.config(text="未找到数据")

if __name__ == "__main__":
    # 设置中文字体支持
    root = tk.Tk()
    # 创建应用实例
    app = ExcelDataAnalyzer(root)
    # 运行主循环
    root.mainloop()